﻿CREATE PROCEDURE [dbo].[friends_get_lising]
    @userId int,
    @page int =0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @email nvarchar(256) = null,
    @login nvarchar(256) = null
AS
    SELECT 
        Users.Avatar,
        Users.Email,
        Users.Login
    FROM Users,Friends
    WHERE
        (Users.UserId = Friends.FriendId and Friends.UserId = @userId)
        and (@email IS NULL OR Users.Email LIKE '%' + @email + '%') 
        and (@login IS NULL OR Users.Login LIKE '%' + @login + '%')
    ORDER BY
        CASE WHEN @column = 'Email' AND @direction = 1 THEN Users.Email END DESC,
        CASE WHEN @column = 'Email' AND @direction = 0 THEN Users.Email END ASC,
        CASE WHEN @column = 'Login' AND @direction = 1 THEN Users.Login END DESC,
        CASE WHEN @column = 'Login' AND @direction = 0 THEN Users.Login END ASC

        OFFSET((@page-1)*@size) ROWS
        FETCH NEXT @size ROWS ONLY;

DECLARE @TotalCount int
SELECT @TotalCount = Count(Users.Login)
FROM Users,Friends
    WHERE
        (Users.UserId = Friends.FriendId and Friends.UserId = @userId)
        and (@email IS NULL OR Users.Email LIKE '%' + @email + '%') 
        and (@login IS NULL OR Users.Login LIKE '%' + @login + '%')

RETURN @TotalCount
